--USE [scm_main_dev]
GO
/****** Object:  StoredProcedure [dbo].[bd_get_data]    Script Date: 2020/3/20 9:43:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if exists (select 1
          from sysobjects
          where  id = object_id('bd_get_data')
          and type in ('P','PC'))
DROP PROCEDURE bd_get_data
GO
CREATE PROCEDURE bd_get_data
@startDate as varchar(20),--查询开始时间
@endDate as VARCHAR(20),--查询结束时间
@deptIds as VARCHAR(max),--选择的部门ids
@searchTxt as VARCHAR(2000),--输入的工号或姓名
@startRow as INT,--分页-开始行数
@endRow as INT,--分页-结束行数
@totalSize as INT OUTPUT
AS
BEGIN

	declare @lstr1 varchar(max)
	declare @lstr2 varchar(max)
	declare @sqlstr varchar(max)
	declare @lstrCount nvarchar(max)
	set @lstr1 = ' ';
	set @lstr2 = ' ';
	set @lstrCount = ' ';

	set @totalSize = 100000
	if @searchTxt = ''
	BEGIN
		select @lstr1=@lstr1 + '[' +t.shift_id + '] as '''+t.bh+''',' ,@lstr2=@lstr2 + '[' +t.shift_id + '],' from (select distinct ltrim(str(shift_id)) as shift_id,mc,bh from bd_kq_timecard t INNER JOIN zt_banci z on t.shift_id = z.id where convert(varchar(10),card_date) BETWEEN @startDate and @endDate and dept_id in(select v.value from string_split(@deptIds,',')v) and t.shift_id>0) as t
	END
	ELSE BEGIN
		select @lstr1=@lstr1 + '[' +t.shift_id + '] as '''+t.bh+''',' ,@lstr2=@lstr2 + '[' +t.shift_id + '],' from (select distinct ltrim(str(shift_id)) as shift_id,mc,bh from bd_kq_timecard t INNER JOIN zt_banci z on t.shift_id = z.id where convert(varchar(10),card_date) BETWEEN @startDate and @endDate and dept_id in(select v.value from string_split(@deptIds,',')v) and (user_no like concat('%',@searchTxt,'%') or user_name like concat('%',@searchTxt,'%')) and t.shift_id>0) as t
	end

	if @lstr1!=' '
	BEGIN
		set @lstr1=left(@lstr1,len(@lstr1)-1)
		set @lstr2=left(@lstr2,len(@lstr2)-1)

		if @searchTxt = ''
		BEGIN

			set @sqlstr='select * from (select user_no as userNo,user_serial as userSerial,user_name as userName,dept_name as deptName,card_date as swipeDate
		,{1},case when swipe_begin>0 then dbo.bd_fn_numToMin3(swipe_begin) else '''+' '+''' end as swipeBegin,case when swipe_end>0 then dbo.bd_fn_numToMin3(swipe_end) else '''+' '+''' end as swipeEnd ,ROW_NUMBER() OVER(Order by card_date desc ) AS RowId from (select user_no,user_serial,user_name,dept_name ,card_date,shift_id,swipe_begin,swipe_end from bd_kq_timecard where  convert(varchar(10),card_date) BETWEEN '''+@startDate +''' and '''+ @endDate +''' and shift_id>0 and dept_id in('+@deptIds+')) a  pivot(count(shift_id) for shift_id  in ({2})) as T) w where RowId between '+STR(@startRow)+' and '+STR(@endRow)+';'
		set @lstrCount='select @count = count(1) from (select user_no as userNo,user_serial as userSerial,user_name as userName,dept_name as deptName,card_date as swipeDate
		,{1},case when swipe_begin>0 then dbo.bd_fn_numToMin3(swipe_begin) else '''+' '+''' end as swipeBegin,case when swipe_end>0 then dbo.bd_fn_numToMin3(swipe_end) else '''+' '+''' end as swipeEnd from (select user_no,user_serial,user_name,dept_name ,card_date,shift_id,swipe_begin,swipe_end from bd_kq_timecard where  convert(varchar(10),card_date) BETWEEN '''+@startDate +''' and '''+ @endDate +''' and shift_id>0 and dept_id in('+@deptIds+')) a  pivot(count(shift_id) for shift_id  in ({2})) as T) w ;'


		END
		ELSE BEGIN
			set @sqlstr='select * from (select user_no as userNo,user_serial as userSerial,user_name as userName,dept_name as deptName,card_date as swipeDate
		,{1},case when swipe_begin>0 then dbo.bd_fn_numToMin3(swipe_begin) else '''+' '+''' end as swipeBegin,case when swipe_end>0 then dbo.bd_fn_numToMin3(swipe_end) else '''+' '+''' end as swipeEnd ,ROW_NUMBER() OVER(Order by card_date desc ) AS RowId from (select user_no,user_serial,user_name,dept_name ,card_date,shift_id,swipe_begin,swipe_end from bd_kq_timecard where  convert(varchar(10),card_date) BETWEEN '''+@startDate +''' and '''+ @endDate +''' and shift_id>0 and dept_id in('+@deptIds+') and (user_no like '''+concat('%',@searchTxt,'%') +''' or user_name like '''+concat('%',@searchTxt,'%')+''')) a  pivot(count(shift_id) for shift_id  in ({2})) as T) w where RowId between '+STR(@startRow)+' and '+STR(@endRow)+';'
		set @lstrCount='select @count = count(1) from (select user_no,user_serial,user_name,dept_name ,card_date,shift_id,swipe_begin,swipe_end from bd_kq_timecard where  convert(varchar(10),card_date) BETWEEN '''+@startDate +''' and '''+ @endDate +''' and shift_id>0 and dept_id in('+@deptIds+') and (user_no like '''+concat('%',@searchTxt,'%') +''' or user_name like '''+concat('%',@searchTxt,'%')+''')) a  pivot(count(shift_id) for shift_id  in ({2})) as T;'
		END

		set @sqlstr=replace(@sqlstr,'{1}',@lstr1)
		set @sqlstr=replace(@sqlstr,'{2}',@lstr2)
		set @lstrCount=replace(@lstrCount,'{1}',@lstr1)
		set @lstrCount=replace(@lstrCount,'{2}',@lstr2)
		exec sp_executesql @lstrCount, N'@count int output', @totalSize output
		print @sqlstr
		exec (@sqlstr)

	END


END